package com.java.zhuiyun.api.recommendedCourse.mapper;

import com.java.zhuiyun.api.back_course.entity.BackCourse;
import com.java.zhuiyun.api.recommendedCourse.model.TeacherCourseModel;
import com.java.zhuiyun.api.series_course.model.PcSeriesCourse;
import com.java.zhuiyun.api.teacher.model.TeacherModel;
import com.java.zhuiyun.common.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @InterfaceName RecommendedCourseMapper
 * @Description TODO PC推荐课程持久层
 * @Author WX
 * @Date 2023/8/21 9:24
 **/
@Mapper
public interface RecommendedCourseMapper extends BaseMapper<TeacherCourseModel> {

    /*
     * @Author WX
     * @Description 推荐课程随机查询9条
     * @Date 2023/8/21 9:34
     * @Return
     * @param null
     */
    @Select("SELECT c.*, t.* " +
            "FROM course c, teacher t " +
            "WHERE c.del_flag = 0 AND c.teacher_id = t.id " +
            "ORDER BY (c.course_click / DATEDIFF(NOW(), c.up_time)) DESC " +
            "LIMIT 9")
    List<TeacherCourseModel> randonThree();

    /*
     * @Author WX
     * @Description PC免费课程查询四条
     * @Date 2023/8/21 14:11
     * @Return
     * @param null
     */
    @Select("SELECT * FROM course c,teacher t WHERE c.del_flag = 0 AND c.pay_type = 0 AND c.teacher_id = t.id ORDER BY RAND() " +
            "LIMIT 4")
    List<TeacherCourseModel> randonFour();

    /*
     * @Author WX
     * @Description PC新课推荐
     * @Date 2023/8/21 15:33
     * @Return
     * @param null
     */
    @Select("SELECT * FROM course c, teacher t WHERE c.del_flag = 0  AND c.teacher_id = t.id ORDER BY c.up_time DESC LIMIT 4")
    List<TeacherCourseModel> randonNewFour();

    /*
     * @Author WX
     * @Description PC精选好课
     * @Date 2023/8/21 16:06
     * @Return
     * @param null
     */
    @Select("SELECT * FROM course c, teacher t WHERE c.del_flag = 0  AND c.teacher_id = t.id ORDER BY c.course_click DESC LIMIT 4")
    List<TeacherCourseModel> randonGoodFour();

    /*
     * @Author WX
     * @Description PC系列课程
     * @Date 2023/8/21 16:25
     * @Return
     * @param null
     */
    @Select("SELECT s.*,t.teacher_name FROM series_course s,teacher t WHERE s.teacher_id = t.id AND s.course_ids IS NOT NULL AND s.del_flag = 0 ORDER BY RAND() LIMIT 4")
    List<PcSeriesCourse> randonSeriesFour();

    /*
     * @Author WX
     * @Description PC精品课
     * @Date 2023/8/21 16:07
     * @Return
     * @param null
     */
    @Select("SELECT * FROM course c, teacher t WHERE c.del_flag = 0  AND c.teacher_id = t.id AND pay_type = 1 ORDER BY c.course_click DESC LIMIT 4")
    List<TeacherCourseModel> randonClsaaicFour();

    /*
     * @Author WX
     * @Description PC直播
     * @Date 2023/8/21 16:07
     * @Return
     * @param null
     */
    @Select("SELECT * FROM live l, sys_user u WHERE l.del_flag = 0 AND l.live_status = 0 AND l.live_room_status = 0 AND l.teacher_id = u.id  LIMIT 4")
    List<TeacherCourseModel> publicClasses();

    /*
     * @Author WX
     * @Description 推荐课程随机模糊查询三条
     * @Date 2023/8/21 9:34
     * @Return
     * @param subjectId
     */
    @Select("SELECT * FROM course c, teacher t WHERE c.subject_id = #{subjectId} AND c.del_flag = 0 AND c.teacher_id = t.id ORDER BY RAND() LIMIT 3")
    List<TeacherCourseModel> randonThreeY(String subjectId);

    /*
     * @Author WX
     * @Description PC微专业
     * @Date 2023/8/22 11:15
     * @Return
     * @param null
     */
    @Select("SELECT * FROM course c, teacher t,sys_dict d WHERE c.del_flag = 0 AND c.teacher_id = t.id AND c.subject_id = d.id AND d.prent_id =20 LIMIT 4")
    List<TeacherCourseModel> selectMicroMajor();

    /*
     * @Author WX
     * @Description 单系列所有课程查询
     * @Date 2023/8/31 10:25
     * @Return
     * @param pcSeriesCourse
     */
    @Select("<script>SELECT c.*, t.teacher_name, d.dict_name" +
            " FROM course c, teacher t, sys_dict d" +
            " WHERE c.teacher_id = t.id" +
            " AND c.subject_id = d.id" +
            " AND c.id IN" +
            " <foreach item='item' collection='courseIdList' open='(' separator=',' close=')'>" +
            " #{item}" +
            " </foreach></script>")
    List<PcSeriesCourse> getSeriesCourseById(@Param("courseIdList")List<String> courseIdList);

    /*
     * @Author WX
     * @Description 系列课程综查询总金额
     * @Date 2023/8/31 10:27
     * @Return
     * @param pcSeriesCourse
     */
    @Select("<script>SELECT * " +
            " FROM course " +
            " WHERE id IN" +
            " <foreach item='item' collection='pricelist' open='(' separator=',' close=')'>" +
            " #{item}" +
            " </foreach></script>")
    List<PcSeriesCourse> getSeriesCoursePrice(@Param("pricelist")List<String> pricelist);
}
